drop proc [spa_SEC_Login]                  
GO
/*'-------------------------------------------------------------------------------------------------*                    
* PROCEDURE: [spa_SEC_Login]  
* PURPOSE: Validate the access to front-end website.  
* NOTES:  
* CREATED: Nha Doan 04/14/2011  
* MODIFIED:   
  
  
' Date       | Version | Ticket #    |       Author             | Description                    
' ========== | ======= | =========== | =========================| ====================================================================                    
' 04/14/2011 |  1.00   | 16131       | Nha Doan                 | CREATED NEW.                        
'-------------------------------------------------------------------------------------------------*                    
*/                    
-- [spa_SEC_Login] @ClientId=1,@LoginName='sysadmin',@UserPassword='6E-9B-3A-76-20-AA-F7-7F-36-27-75-15-09-77-EE-B8'
create proc [spa_SEC_Login]                  
 @ClientId int,                    
 @LoginName varchar(30),                    
 @UserPassword varchar(100),                    
 @ASPLog_ClientIPAddr varchar(20) = 'none',                      
 @ASPLog_HostIPAddr varchar(20) = 'none',                      
 @ASPLog_BrowserType varchar(250) = 'none'                    
                    
AS     
/*               
return 
	1: successful
	2: password nearly expired
	3: sys password is expired
	4: user password is expired
	5: locked out user
	6: inactive user
	7: invalid user
	8: max login attempt have been reached
	9: invalid password
	10: valid sys password
*/

declare
 @UserRecId uniqueidentifier,
 @CurrentUserPassword varchar(100),                      
 @LogUserPassword varchar(100),                      
 @UserPasswordType int,                      
 @UserPasswordDTS datetime,                      
 @LoginAttempts int,                      
 @LastLoginDTS datetime,
 @UserStatus varchar(1),                      
 @LoginOptionId int,                      
 @LoginAction int,                      
 @LoginActionText varchar(100),                      
 @UserNameFirst varchar(30),                      
 @UserNameLast  varchar(30),                      
 @PrevLoginDTS datetime,                    
                    
 @ExecDTS datetime,                    
 @SystemPwdDuration int,                    
 @MaxAttemptLogin int,                    
 @UserPwdDuration int,                    
 @UserPwdExpiredWarningDuration int
--prepare values to validate user                    


set @ExecDTS = getdate()
select @MaxAttemptLogin = cast([Value] as int) from REF_Configs (nolock) where [Key] = 'MaxLoginAttempts' and ClientId = @ClientId
select @SystemPwdDuration = cast([Value] as int) from REF_Configs (nolock) where [Key] = 'SystemPwdDuration' and ClientId = @ClientId
select @UserPwdDuration = cast([Value] as int) from REF_Configs (nolock) where [Key] = 'UserPwdDuration' and ClientId = @ClientId
select @UserPwdExpiredWarningDuration = cast([Value] as int) from REF_Configs (nolock) where [Key] = 'UserPwdExpiredWarningDuration' and ClientId = @ClientId

                     
select @CurrentUserPassword = UserPassword,                      
 @UserPasswordType = UserPasswordType,                      
 @UserPasswordDTS = convert(datetime,convert(varchar,UserPasswordDTS,101),101),
 @LoginAttempts = LoginAttempts,
 @LastLoginDTS = LastLoginDTS,
 @UserStatus = Status,
 @UserRecId = RecId,
 @UserNameFirst = UserNameFirst,
 @UserNameLast = UserNameLast
from SEC_Users (nolock)                   
where (ClientId = @ClientId or @ClientId = 0)
 and LoginName = @LoginName                     
                    

if(@UserStatus is null) set @LoginAction = 7 -- invalid user
else if(@UserStatus= 0 or @UserStatus = 1) set @LoginAction = 6 -- inactive user
else if(@UserStatus= 3) set @LoginAction = 5 -- user is locked out
else if(@LoginAttempts >= @MaxAttemptLogin) set @LoginAction = 8 -- max login attempts exceeded
else if(@CurrentUserPassword = @UserPassword) -- valid password
begin                    
 set @LogUserPassword = null
 if( @SystemPwdDuration > 0 and @UserPasswordType=1 and @ExecDTS > dateadd(dd,@SystemPwdDuration,@UserPasswordDTS) )
 begin                    
  set @LoginAction = 3 -- system password is expired
 end                    
 else if(@UserPwdDuration>0 and @UserPasswordType=2 and @ExecDTS > dateadd(dd,@UserPwdDuration,@UserPasswordDTS))-- password reset by admin is expired                    
 begin                    
  set @LoginAction = 4 -- user password is expired
 end                    
else if(@UserPwdExpiredWarningDuration > 0 and @UserPasswordType=2  and @ExecDTS >= dateadd(dd,@UserPwdDuration - @UserPwdExpiredWarningDuration,@UserPasswordDTS))      
 begin      
  set @LoginAction = 2 --user's password nearly expired      
 end                    
 else                    
 begin
  if @UserPasswordType = 1
  set @LoginAction = 10 -- Successful Login with sys pass
  else
  set @LoginAction = 1 -- Successful Login                    
                      
 end                    
end                    
else               
begin                    
 set @LoginAction = 9  -- invalid password
 set @LogUserPassword = @UserPassword
end                    
                    
--write log                    
declare @error int                    
set @error = 0                    
begin tran                    
if(@LoginAction = 1 
	or @LoginAction = 2
	or @LoginAction = 10
	or @LoginAction = 4
) --login successfully                    
begin                    
                     
                    
 update SEC_Users                     
 set LoginAttempts = 0,                     
  PrevLoginDTS = @LastLoginDTS,                     
  LastLoginDTS = @ExecDTS
 where  RecId = @UserRecId
 set @error = @error + @@error                    
end                    
else --login failed                    
begin                    
 update SEC_Users
 set         
  LoginAttempts = LoginAttempts + 1
 where RecId = @UserRecId
 set @error = @error + @@error                    
end                    
                    
                    
                    
                    
                    
                
insert into LOG_UserLoginTransactions (                      
 TransDTS,
 ReturnCode,                      
 ClientId,
 LoginName,
 UserPassword,                      
 UserNameFirst,                      
 UserNameLast,                      
 UserPasswordType,                      
 UserPasswordDTS,                      
 LoginAttempts,                      
 LastLoginDTS,                      
 UserStatus,                       
 ClientIPAddr,                      
 HostIPAddr,                      
 BrowserType                    
)                     
values (                     
 @ExecDTS,
 @LoginAction,                      
 @ClientId,                     
 @LoginName,
 @LogUserPassword,
 @UserNameFirst,                     
 @UserNameLast,                     
 @UserPasswordType,                     
 @UserPasswordDTS,                           
 @LoginAttempts,                    
 @LastLoginDTS,                     
 @UserStatus,                     
 @ASPLog_ClientIPAddr,                     
 @ASPLog_HostIPAddr,                     
 @ASPLog_BrowserType  
)                    
set @error = @error + @@error                    
if (@error>0)                    
begin                    
 rollback tran -- operation has some errors, rollback all                    
 select -1                    
end                    
else                    
begin                    
 commit tran -- successfull                    
 set @UserPwdExpiredWarningDuration = datediff(dd,@ExecDTS,dateadd(dd,@UserPwdExpiredWarningDuration,@UserPasswordDTS))      
 select @LoginAction as LoginResult,@UserPwdExpiredWarningDuration as ExpiredDays      
end
GO